﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace isky.dapper
{
    public class RFMRecencySql
    {
        /// <summary>
        /// 查询近度规则最大天数
        /// </summary>
        /// <returns></returns>
        public int ListRFMRecencyDaysnum()
        {
            string cmdText = @"select max(new_daysnum) as daysnum  from new_RFMRecencyBase";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.ExecuteScalar<int>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }

     

        /// <summary>
        /// 查询最近一次 近度 规则的 天数和分值
        /// </summary>
        /// <returns></returns>
        public IEnumerable<dynamic> ListRFMRecencyDayValues()
        {
            string cmdText = @"select rf.new_DaysNum, rf.new_Value  from new_RFMRecencyBase  as rf
                                 where rf.new_rfmrecencyversion=(
                                 select top 1 new_RFMRecencyVersionId from new_RFMRecencyVersionBase as rv
                                 order by rv.CreatedOn desc
                                 )
                                 order by rf.new_daysnum asc";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.Query(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }

        /// <summary>
        /// 查询所有客户最近一次订单的订单编号
        /// 订单状态 100000001生效 100000005已完成
        /// 客户性质:1 正式客户
        /// </summary>
        /// <returns></returns>
        public IEnumerable<dynamic> ListOrderNumber(int day)
        {
            string cmdText = $@"select * from (select ord.new_fieldname as ordernumber,
                                              acc.new_kehuxingzhi,
                                              acc.accountnumber,
                                              acc.accountId,
                                              ord.new_status,ROW_NUMBER() OVER(PARTITION BY customerid ORDER BY new_dingdanchengjiaoriqi desc) AS ROWID from SalesOrderBase as ord
                                inner join accountbase as acc on acc.AccountId=ord.customerid
                                 where acc.new_kehuxingzhi=1  
                                 and (ord.new_status ='100000001' or ord.new_status ='100000005')
                                 and datediff(day,dateadd(hh,8,new_dingdanchengjiaoriqi),getdate())<{day}
                                ) a 
                                where  ROWID =1";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.Query(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }

        /// <summary>
        ///  查询订单最近天数
        /// </summary>
        /// <param name="recendday">近度版本表中的天数</param>
        /// <param name="number">订单编号</param>
        /// <returns></returns>
        public int GetOrderRecenddays(int recendday, string number)
        {
            string cmdText = @"SELECT datediff(day, new_dingdanchengjiaoriqi, getdate()) as drecencyday FROM SalesOrderBase
                                 /**where**/";
            SqlBuilder sqlbuilder = new SqlBuilder();
            sqlbuilder.Where($@"datediff(day,dateadd(hh,8,new_dingdanchengjiaoriqi),getdate())<{recendday}");
            sqlbuilder.Where("new_fieldname=@number", new { number = number });
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.ExecuteScalar<int>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }



        /// <summary>
        /// 查询所有每个客户订单的总数
        /// 订单状态 100000001生效 100000005已完成
        /// </summary>
        /// 客户性质:1 正式客户
        /// <param name="days">最近版本天数</param>
        /// <returns></returns>
        public IEnumerable<dynamic> ListOrderCount(int days)
        {
            string cmdText = @"select acc.AccountNumber, COUNT(*) as ordercount,acc.accountId from SalesOrderBase ord
                                inner join accountbase as acc on acc.AccountId=ord.customerid
                                /**where**/
                                group by acc.AccountNumber,acc.accountId";
            SqlBuilder sqlbuilder = new SqlBuilder();
            sqlbuilder.Where("acc.new_kehuxingzhi=1 and(ord.new_status = '100000001' or ord.new_status = '100000005') ");
            sqlbuilder.Where($@"datediff(day,dateadd(hh,8,new_dingdanchengjiaoriqi),getdate())<{days}");
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.Query(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }
        /// <summary>
        /// 查询所有客户订单的总数
        /// 订单状态 100000001生效 100000005已完成</summary>
        /// 客户性质:1 正式客户
        /// <param name="versionday">最近版本天数</param>
        /// <returns></returns>
        public int ListOrderSum(int versionday)
        {
            string cmdText = @"select COUNT(*) as ordercount from SalesOrderBase ord
                                inner join accountbase as acc on acc.AccountId=ord.customerid
                                /**where**/";
            SqlBuilder sqlbuilder = new SqlBuilder();
            sqlbuilder.Where("acc.new_kehuxingzhi=1 and(ord.new_status = '100000001' or ord.new_status = '100000005') ");
            sqlbuilder.Where($@"datediff(day,dateadd(hh,8,new_dingdanchengjiaoriqi),getdate())<{versionday}");
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.ExecuteScalar<int>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }

        /// <summary>
        /// 查询最近一次 频度 规则的 分值,条件符号
        /// </summary>
        /// <returns></returns>
        public IEnumerable<dynamic> ListFrequencyCriteriaValues()
        {
            string cmdText = @" select new_criteria, new_value from new_RFMFrequencyBase as fr
                                where fr.new_rfmfrequencyversion =(
                                select top 1 new_RFMFrequencyVersionId from new_RFMFrequencyVersionBase as fv
                                order by fv.CreatedOn desc
                                )";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.Query(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }

        /// <summary>
        /// 查询最近频度版本的天数
        /// </summary>
        /// <returns></returns>
        public int ListFrequencyVersionDay()
        {
            string cmdText = @" select fv.new_daysnum from  new_RFMFrequencyVersionBase as fv 
                                order by fv.CreatedOn desc";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.ExecuteScalar<int>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }


        /// <summary>
        /// 查询最近销售度天数所有每一个客户的订单总金额
        /// </summary>
        /// <param name="days">天数</param>
        /// <returns></returns>
        public IEnumerable<dynamic> ListAccountOrderTotalAmount(int days)
        {
            string cmdText = $@"create table #tab(
                                AccountNumber nvarchar(2000),
                                accountId uniqueidentifier,
                                ordersumTol numeric(18,1)--总金额
                                )
                                 declare My_cursor CURSOR
                                 for(
                                     --查询每个客户的订单总金额
                                    select acc.AccountNumber,
                                           acc.accountId,
                                          sum(new_zongjine) as ordersum from SalesOrderBase ord
                                    inner join accountbase as acc on acc.AccountId=ord.customerid
                                    where acc.new_kehuxingzhi=1  
                                     and(ord.new_status = '100000001' or ord.new_status = '100000005')
                                     and datediff(day,dateadd(hh,8,new_dingdanchengjiaoriqi),getdate())<={days}
                                    group by acc.AccountNumber,acc.accountId )
                                    open My_Cursor; --打开游标
                                    DECLARE @ordersum numeric(18,1),@accountId uniqueidentifier, @ordersumTol numeric(18,1), @AccountNumber nvarchar(2000)
                                    fetch next from My_Cursor INTO @AccountNumber,@accountId, @ordersum --读取第一行数据 
                                    while  @@FETCH_STATUS = 0  
                                       begin
                                         if(@ordersum%2>0)
                                         begin
                                             set @ordersumTol = (@ordersum / 2)
                                              insert into #tab values (@AccountNumber,@accountId,@ordersumTol)
                                         end
                                         else
		                                 begin
                                           set @ordersumTol=(@ordersum/2)+1
		                                   insert into #tab values (@AccountNumber,@accountId,@ordersumTol)
                                        end
                                fetch next from My_Cursor INTO @AccountNumber,@accountId, @ordersum;--读取下一行数据
                                end
                                select  * from #tab;
                                       close My_cursor; --关闭游标
                                       deallocate My_Cursor; --释放游标 
	                                   drop table #tab; 
                                 ";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                var result= dbconn.Query(sqlTemplate.RawSql, sqlTemplate.Parameters);
                return result;
            }
        }


        /// <summary>
        /// 查询最近销售度天数所有客户的订单总金额
        /// </summary>
        /// <param name="days"></param>
        /// <returns></returns>
        public double ListOrderTotalAmount(int days)
        {
            string cmdText = $@"create table #tab1(
                                ordersumTol numeric(18,1)--总金额
                                )
                                 declare My_cursor CURSOR
                                 for(
                                     --查询每个客户的订单总金额
                                    select sum(new_zongjine) as ordersum from SalesOrderBase ord
                                    inner join accountbase as acc on acc.AccountId=ord.customerid
                                    where acc.new_kehuxingzhi=1  
                                         and(ord.new_status = '100000001' or ord.new_status = '100000005')
                                         and datediff(day,dateadd(hh,8,new_dingdanchengjiaoriqi),getdate())<={days})
                                    open My_Cursor; --打开游标
                                    DECLARE @ordersum numeric(18,1), @ordersumTol numeric(18,1)
                                    fetch next from My_Cursor INTO  @ordersum --读取第一行数据 
                                    while  @@FETCH_STATUS = 0  
                                       begin
                                         if(@ordersum%2>0)
                                         begin
                                             set @ordersumTol = (@ordersum / 2)
                                              insert into #tab values (@ordersumTol)
                                         end
                                         else
		                                 begin
                                           set @ordersumTol=(@ordersum/2)+1
		                                   insert into #tab1 values (@ordersumTol)
                                        end
                                fetch next from My_Cursor INTO  @ordersum;--读取下一行数据
                                end
                                select  * from #tab1
                                       close My_cursor; --关闭游标
                                       deallocate My_Cursor; --释放游标 
	                                   drop table #tab1;
	                                   ";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.ExecuteScalar<double>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }

     
        /// <summary>
        /// 每个客户订单的总金额
        /// </summary>
        /// <param name="days"></param>
        /// <returns></returns>
        public IEnumerable<dynamic> ListFmmonetaryEachSumAmount(int days)
        {
            string cmdText = $@"select acc.AccountNumber,
                             acc.accountId,
                             sum(new_zongjine) as ordersum from SalesOrderBase ord
                              inner join accountbase as acc on acc.AccountId=ord.customerid
                              where acc.new_kehuxingzhi=1  
                               and(ord.new_status = '100000001' or ord.new_status = '100000005')
                               and datediff(day, dateadd(hh,8, new_dingdanchengjiaoriqi),getdate())<{days}
                              group by acc.AccountNumber,acc.accountId
                              order by ordersum desc ";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.Query(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }

        /// <summary>
        /// 查询最近销售度版本的天数
        /// </summary>
        /// <returns></returns>
        public int ListFmmonetaryVersionDay()
        {
            string cmdText = @" select fm.new_daysnum from  new_rfmmonetaryversionBase as fm 
                                order by fm.CreatedOn desc";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.ExecuteScalar<int>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }


        /// <summary>
        /// 查询最近一次 销售度 规则的 分值,条件符号
        /// </summary>
        /// <returns></returns>
        public IEnumerable<dynamic> ListFmmonetaryCriteriaValues()
        {
            //string cmdText = @" select  new_criteria, new_value from new_RFMMonetaryBase as fm
            //                    inner join new_rfmmonetaryversionBase as fy on fm.new_rfmmonetaryversion= fy.new_rfmmonetaryversionId
            //                    order by fm.CreatedOn desc";
            string cmdText = @"select  new_criteria, new_value from new_RFMMonetaryBase as fm
                    where fm.new_rfmmonetaryversion= (
                    select top 1 new_RFMMonetaryVersionId from new_rfmmonetaryversionBase as fy order by fy.CreatedOn desc
                    )";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.Query(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }
        /// <summary>
        /// 查询所有分类规则
        /// </summary>
        /// <returns></returns>
        public IEnumerable<dynamic> ListRFMClassificationRules()
        {
            string cmdText = @"select new_name, new_rsclassify, new_fsclassify, new_msclassify from new_RFMModelBase as rfmmodel";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.Query(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }

        public Guid GetAccountGuid(string accountnumber)
        {
            string cmdText = @"select accountId from accountBase /**where**/";
            SqlBuilder sqlbuilder = new SqlBuilder();
            sqlbuilder.Where($@"accountnumber=@accountnumber",new {accountnumber=accountnumber });
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.ExecuteScalar<Guid>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }

        /// <summary>
        /// 查询客户的总数
        /// </summary>
        /// <param name="days"></param>
        /// <returns></returns>
        public int GetAccountCount(int days)
        {
            string cmdText = $@"select COUNT(a.account) as accountcount from(
                             select distinct accountnumber as account from accountbase as acc
                             inner join SalesOrderBase ord on acc.AccountId= ord.customerid
                             WHERE acc.new_kehuxingzhi= 1 and(ord.new_status = '100000001' or ord.new_status = '100000005')
                                AND datediff(day, dateadd(hh,8, new_dingdanchengjiaoriqi),getdate())<{days}
                                group by accountnumber
                                ) as a ";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.ExecuteScalar<int>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }

        public IEnumerable<dynamic> ListAccount()
        {
            string cmdText = @"select top 1 * from accountBase /**where**/";
            SqlBuilder sqlbuilder = new SqlBuilder();
         
            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.Query(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }


        public IEnumerable<dynamic> ListAccounttest()
        {
            string cmdText = @" select acc.accountid,acc.creditlimit from  accountbase as acc where  StatusCode = 1";
            SqlBuilder sqlbuilder = new SqlBuilder();

            var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
            using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
            {
                return dbconn.Query(sqlTemplate.RawSql, sqlTemplate.Parameters);
            }
        }
    }
}
